import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * java -cp bin:lib/sqlitejdbc-v056.jar SqliteTest
 * 
 * Download from:
 * http://www.zentus.com/sqlitejdbc/
 */
public class SqliteTest
{
    static public final String HOST = "localhost";
    static public final String DB_NAME = "test.db";
    static public final String USER = "";
    static public final String PASS = "";
    
    public static void main(String args[]){
        Statement stmt;

        //Register the JDBC driver for MySQL.
        try {
            Class.forName("org.sqlite.JDBC");

            String url = "jdbc:sqlite:" + DB_NAME;

            Connection con = DriverManager.getConnection(url);

            //Display URL and connection information
            System.out.println("URL: " + url);
            System.out.println("Connection: " + con);

            //Get a Statement object
            stmt = con.createStatement();
            
            System.out.print("Create table : ");
            int result = stmt.executeUpdate("CREATE TABLE IF NOT EXISTS \"people\" (" +
                    "\"id\" INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "\"name\" VARCHAR(45) NULL," +
                    "\"size\" FLOAT NULL," +
                    "\"age\" INT NULL);");
            System.out.print(result + "\n"); // 0
            
            System.out.print("Deleting a record : ");
            result = stmt.executeUpdate("DELETE FROM people WHERE id > 0;");
            System.out.print(result + "\n");
            
            System.out.print("Adding a record : ");
            result = stmt.executeUpdate("INSERT INTO people (name,size,age) VALUES ('David',3.15,28);");
            result = stmt.executeUpdate("INSERT INTO people (name,size,age) VALUES ('David',3.15,28);");
            result = stmt.executeUpdate("INSERT INTO people (name,size,age) VALUES ('David',3.15,28);");
            result = stmt.executeUpdate("INSERT INTO people (name,size,age) VALUES ('David',3.15,28);");
            result = stmt.executeUpdate("INSERT INTO people (name,size,age) VALUES ('David',3.15,28);");
            System.out.print(result + "\n");
            
            System.out.println("* List of people:");
            ResultSet results = stmt.executeQuery("SELECT * FROM people");
            while (results.next()) {
                System.out.print( "\t" + results.getString(2) );
                System.out.println("");
            }
            
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
